import db

#功能实现
class controller:
    global dat  # 创建全局dat
    dat = db.pydb()  # 创建数据库对象
    def cha(self, id):  # 对学号进行模糊查询
        self.id = id
        sql = "select * from student where id like '%" + self.id + "%'"
        x = list(dat.datadb(sql))  # 获取数据 将数据由元组改为列表
        for i in range(len(x)):  # 将每一层数据更改成列表
            x[i] = list(x[i])
            n = x[i][3] + x[i][4] + x[i][5]  # 计算总成绩
            x[i].append(n)
        return x

    def pai(self):  # 对成绩排名
        sql = "select * from student"
        x = list(dat.datadb(sql))  # 获取数据 将数据由元组改为列表
        for i in range(len(x)):  # 将每一层数据更改成列表
            x[i] = list(x[i])
            n = x[i][3] + x[i][4] + x[i][5]  # 计算总成绩
            x[i].append(n)
        for i in range(1, len(x)):
            # 从第二个元素开始，每次取出一个元素，插入前面的序列使其有序
            for j in range(i, 0, -1):  # 插入排序
                if x[j][6] > x[j - 1][6]:
                    x[j], x[j - 1] = x[j - 1], x[j]
        return x

    def biao(self):
        x = self.pai()  # 调用排序表
        for i in range(len(x)):  # 将数据全部转为str
            for j in range(7):
                x[i][j] = str(x[i][j])
        t = [["学号", "姓名", "性别", "数学", "英语", "语文", "排名"]] + x  # 给index的列表设置表名
        f = open("student.csv", "w")
        for i in range(len(t)):  # 自动换行输出
            for j in range(7):
                f.write(t[i][j] + ',')
            f.write('\n')
        print("完成")
        f.close()

    def zeng(self, s):
        self.s = s
        # try:
        sql = "insert into student values(%d,'%s','%s',%d,%d,%d)" % (
            self.s[0], self.s[1], self.s[2], self.s[3], self.s[4], self.s[5])
        dat.datadb(sql)  # 插入数据

    def geng(self, s):
        self.s = s
        sql = "update student set name ='" + self.s[1] + "',sex='" + self.s[2] + "',math=" + self.s[3] + ",chinese=" + \
              self.s[4] + ",english=" + self.s[5] + " where id =" + self.s[0]
        dat.datadb(sql)  # 更新数据
    def shan(self, n):
        self.n = n
        sql = "delete from student where id=" + self.n
        dat.datadb(sql)  # 删除数据
